package com.guigu.serviceorder.mapper;

import com.guigu.serviceorder.entity.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface CategoryReportMapper extends Mapper<CategoryReport> {

    @Select("SELECT oi.`category_id1`,oi.`category_id2`,oi.`category_id3`, " +
            "DATE_FORMAT (o.`pay_time`,'%Y-%m-%d') count_date,SUM(oi.`num`) num,SUM(oi.`pay_money`) money " +
            "FROM `tb_order_item` oi,`tb_order` o " +
            "WHERE oi.`order_id`=o.`id`  " +
            "AND o.`pay_status`='1' AND o.`is_delete`='0' " +
            "AND DATE_FORMAT (o.`pay_time`,'%Y-%m-%d')= #{date} " +
            "GROUP BY oi.`category_id1`,oi.`category_id2`,oi.`category_id3`, " +
            "DATE_FORMAT (o.`pay_time`,'%Y-%m-%d') ")
//    @Select("SELECT oi.`category_id1`,oi.`category_id2`,oi.`category_id3`,\n" +
//            "DATE_FORMAT (o.`pay_time`,'%Y-%m-%d') count_date,SUM(oi.`num`) num,SUM(oi.`pay_money`) money\n" +
//            "FROM `tb_order_item` oi,`tb_order` o\n" +
//            "WHERE oi.`order_id`=o.`id` \t\n" +
//            "AND o.`pay_status`='1' AND o.`is_delete`='0'\n" +
//            "AND DATE_FORMAT (o.`pay_time`,'%Y-%m-%d')='2019-04-15'\n" +
//            "GROUP BY oi.`category_id1`,oi.`category_id2`,oi.`category_id3`,\n" +
//            "DATE_FORMAT (o.`pay_time`,'%Y-%m-%d')")
    public List<CategoryReport> categoryReport(@Param("date") LocalDate date);


    @Select("SELECT category_id1 categoryId1, c.name categoryName, SUM(num) num, SUM(money) money \n" +
            "            FROM tb_category_report r, v_category1 c \n" +
            "            WHERE r.category_id1=c.id \n" +
            "            AND count_date>=#{date1}\n" +
            "            AND count_date<=#{date2}\n" +
            "            GROUP BY category_id1, c.name")
//    @Select("SELECT category_id1 categoryId1, c.name categoryName, SUM(num) num, SUM(money) money \n" +
//            "            FROM tb_category_report r, v_category1 c \n" +
//            "            WHERE r.category_id1=c.id \n" +
//            "            AND count_date>='2019-04-15'\n" +
//            "            AND count_date<='2019-04-17'\n" +
//            "            GROUP BY category_id1, c.name")
    public List<Map> category1Count(@Param("date1")String date1,@Param("date2")String date2);



    @Select("\n" +
            "SELECT \n" +
            "DATE_FORMAT (o.`pay_time`,'%Y-%m-%d') count_date,SUM(oi.`num`) num,SUM(oi.`pay_money`) money,COUNT(username) usernum,oi.name\n" +
            "FROM `tb_order_item` oi,`tb_order` o\n" +
            "WHERE oi.`order_id`=o.`id` \t\n" +
            "AND o.`pay_status`='1' AND o.`is_delete`='0'\n" +
            "AND DATE_FORMAT (o.`pay_time`,'%Y-%m-%d')>=#{date1} AND DATE_FORMAT (o.`pay_time`,'%Y-%m-%d')<=#{date2}     \n" +
            "GROUP BY oi.name ")
    public List<Map> namenum(@Param("date1")String date1,@Param("date2")String date2);


    @Select("SELECT o.source_type, COUNT(o.source_type) num ,COUNT(*) number ,SUM(i.money) money FROM tb_order o,tb_order_item i WHERE o.id=i.order_id AND o.pay_status=1  \n" +
            "AND o.source_type>0 AND DATE_FORMAT(o.create_time,'%Y-%m')=#{date1} GROUP BY o.source_type ,o.username\n")
    public List<Map> ordersourcenum(@Param("date1")String date1);


}

